package audi.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;

import audi.model.LocalData;
import audi.model.OJChangepass;
import audi.model.OJGetboxip;
import audi.model.OJLogin;

public class WebHelper {
	private static Logger logtag = Logger.getLogger("Querydb");
	public OJLogin Login(String username, String password) 
	{	
		Connection conn = null;
		Boolean isnull = true;
		try {
			Class.forName(LocalData.driverdb).newInstance();
			conn = DriverManager.getConnection(LocalData.url, LocalData.usernamedb, LocalData.passdb);
			System.out.print("Connect success\n");			
		} catch (Exception e) {
			e.printStackTrace();
		}
		Statement stmt = null;
	    String query = "SELECT * FROM user WHERE (Username = '"+username+"' AND Userpassword = '"+password+"')";
	    logtag.info("\n Câu query:" + query);
//	    String query = "SELECT * FROM user WHERE Useremail = '@emaillll'";
	    OJLogin mResult = new OJLogin();
	    String group = null;
	    try {
	        stmt = conn.createStatement();		        	
	        ResultSet rs = stmt.executeQuery(query);	        
	        logtag.info("\n Bắt đầu excute query" + username);
	        while (rs.next()) {
	        	isnull = false;
                String lastName = rs.getString("Username");
                group = rs.getString("Usergroup");
                System.out.println(lastName);
                logtag.info("\n Giá trị lấy về: "+ lastName);
            }
	        if (isnull ==false)
	        {	logtag.info("\nSuccess:");
	        	mResult.setMess("Đăng nhập thành công");
	        	mResult.setResult("success");
	        	mResult.setIssuccess(true);
	        	mResult.setEmail(username);
	        	mResult.setGroup(group);
	        }
	        else 
	        {
	        	logtag.info("\n Excute không tìm thấy kết quả:");
	        	mResult.setMess("Đăng nhập thất bại, vui lòng kiểm tra ID, Pass");
	        	mResult.setResult("failed");
	        	mResult.setIssuccess(false);
	        }
	        logtag.info("\n Kết thúc");
//	        if (rs.next())
//	        {	logtag.info("\nSuccess:");
//	        	mResult.setMess("Đăng nhập thành công");
//	        	mResult.setResult("success");	        	
//	        }
//	        else 
//	        {	
//	        	logtag.info("\n Excute không tìm thấy kết quả:");
//	        	mResult.setMess("Đăng nhập thất bại, vui lòng kiểm tra ID, Pass");
//	        	mResult.setResult("failed");
//	        }
	        
	    } catch (Exception e) {
//	       System.out.print("\nFailed"+e);
	       logtag.info("\n Lỗi khi excute login - line 67: " +e);
	    } finally {
	        if (stmt != null) {
	        	try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
	        	}
	    }
	    logtag.info("\n Giá trị result: "+ mResult.getMess() +" Result:"+mResult.getResult()+ " issucess:"+mResult.getIssuccess());
		return mResult;
	}
	
	public OJLogin Register(String username, String password, String Group) 
	{	
		Connection conn = null;
		Boolean isnull = true;
		try {
			Class.forName(LocalData.driverdb).newInstance();
			conn = DriverManager.getConnection(LocalData.url, LocalData.usernamedb, LocalData.passdb);
			System.out.print("Connect success\n");			
		} catch (Exception e) {
			e.printStackTrace();
		}
		Statement stmt;
			stmt = null;
		
	    String query = "INSERT INTO  user (Username, Userpassword, Usergroup) VALUES  ('+"+username+"','"+password+"','"+Group+"')";
	    String queryy ="INSERT INTO user (Username, Userpassword, Usergroup) SELECT * FROM (SELECT '"+username+"', '"+password+"', '"+Group+"') AS tmp	WHERE NOT EXISTS (	SELECT Username FROM user WHERE Username = '"+username+"') LIMIT 1";
	    
	    
	    logtag.info("\n Câu insert:" + queryy);
//	    String query = "SELECT * FROM user WHERE Useremail = '@emaillll'";
	    OJLogin mResult = new OJLogin();
	    String group = null;
	    try { 	        	
	    	stmt = conn.createStatement();
//	        ResultSet rs = stmt.executeUpdate(query);	        
	        logtag.info("\n Bắt đầu excute query" + username);
	        int i = stmt.executeUpdate(queryy);	
	        logtag.info("\n Trả về" + i);
	        if (i!=0)
	        {
	        	logtag.info("\nSuccess:");
	        	mResult.setMess("Đăng ký thành công");
	        	mResult.setResult("success");
	        	mResult.setIssuccess(true);
	        	mResult.setEmail(username);
	        	mResult.setGroup(group);
	        }
	        else 
	        {
	        	logtag.info("\n không insert được:");
	        	mResult.setMess("Đăng ký thất bại, vui lòng kiểm tra ID, Pass");
	        	mResult.setResult("failed");
	        	mResult.setIssuccess(false);
	        }
//	        while (rs.next()) {
//	        	isnull = false;
//                String lastName = rs.getString("Username");
//                group = rs.getString("Usergroup");
//                System.out.println(lastName);
//                logtag.info("\n Giá trị lấy về: "+ lastName);
//            }
//	        if (isnull ==false)
//	        {	logtag.info("\nSuccess:");
//	        	mResult.setMess("Đăng ký thành công");
//	        	mResult.setResult("success");
//	        	mResult.setIssuccess(true);
//	        	mResult.setEmail(username);
//	        	mResult.setGroup(group);
//	        }
//	        else 
//	        {
//	        	logtag.info("\n không insert được:");
//	        	mResult.setMess("Đăng ký thất bại, vui lòng kiểm tra ID, Pass");
//	        	mResult.setResult("failed");
//	        	mResult.setIssuccess(false);
//	        }
	        logtag.info("\n Kết thúc");
//	        if (rs.next())
//	        {	logtag.info("\nSuccess:");
//	        	mResult.setMess("Đăng nhập thành công");
//	        	mResult.setResult("success");	        	
//	        }
//	        else 
//	        {	
//	        	logtag.info("\n Excute không tìm thấy kết quả:");
//	        	mResult.setMess("Đăng nhập thất bại, vui lòng kiểm tra ID, Pass");
//	        	mResult.setResult("failed");
//	        }
	        
	    } catch (Exception e) {
//	       System.out.print("\nFailed"+e);
	       logtag.info("\n Lỗi khi excute login - line 148: " +e);
	    } finally {
	        if (stmt != null) {
	        	try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
	        	}
	    }
	    logtag.info("\n Giá trị result: "+ mResult.getMess() +" Result:"+mResult.getResult()+ " issucess:"+mResult.getIssuccess());
		return mResult;
	}
	
	public OJGetboxip GetBoxip(String group) 
	{	
		OJGetboxip mResult = null;
		Connection conn = null;
		try {
			Class.forName(LocalData.driverdb).newInstance();
			conn = DriverManager.getConnection(LocalData.driverdb
					+ LocalData.dbname, LocalData.usernamedb, LocalData.passdb);
			System.out.print("Connect success");
			
//			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		Statement stmt = null;
	    String query = "SELECT * FROM user WHERE (Usergroup ='"+group+"' AND IsBox = '1')";
	    try {
	        stmt = conn.createStatement();	     
//	        stmt.execute(query);
	        ResultSet rs = stmt.executeQuery(query);	        
	        if (rs!=null)
	        {
	        	while (rs.next())
	            {
	              String ip = rs.getString("DeviceIp");
	              String groupp = rs.getString("Usergroup");
	              mResult.setIp(ip);
	              mResult.setGroup(groupp);
	            }
	          }	          	        
	        else 
	        {
	        	mResult.setMess("Lỗi khi lấy IP và Pass");	        	
	        }
	    } catch (SQLException e ) {
	       System.out.print("Failed");
	    } finally {
	        if (stmt != null) {
	        	try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
	        	}
	    }
		return mResult;
	}
	/*
	 * changepass
	 */
	public OJChangepass Changepass(String username, String password, String newpass) 
	{	
		Connection conn = null;
		Boolean isnull = true;
		try {
			Class.forName(LocalData.driverdb).newInstance();
			conn = DriverManager.getConnection(LocalData.url, LocalData.usernamedb, LocalData.passdb);
			System.out.print("Connect success\n");			
		} catch (Exception e) {
			e.printStackTrace();
		}
		Statement stmt;
			stmt = null;		
	    String query = "INSERT INTO  user (Username, Userpassword, Usergroup) VALUES  ('+"+username+"','"+password+"','"+newpass+"')";
	    String queryy ="INSERT INTO user (Username, Userpassword, Usergroup) SELECT * FROM (SELECT '"+username+"', '"+password+"') AS tmp	WHERE NOT EXISTS (	SELECT Username FROM user WHERE Username = '"+username+"') LIMIT 1";	    	    
	    String queryyy="UPDATE user set Userpassword='"+newpass+"' WHERE (Username='"+username+"' AND Userpassword='"+password+"')";
	    logtag.info("\n Câu insert:" + queryy);
//	    String query = "SELECT * FROM user WHERE Useremail = '@emaillll'";
	    OJChangepass mResult = new OJChangepass();
	    String group = null;
	    try { 	        	
	    	stmt = conn.createStatement();
//	        ResultSet rs = stmt.executeUpdate(query);	        
	        logtag.info("\n Bắt đầu excute query" + username);
	        int i = stmt.executeUpdate(queryyy);	
	        logtag.info("\n Trả về" + i);
	        if (i!=0)
	        {
	        	logtag.info("\nSuccess:");
	        	mResult.setMess("Đổi pass thành công");
	        	mResult.setResult("success");
	        	mResult.setIssuccess(true);
	        }
	        else 
	        {
	        	logtag.info("\n không insert được:");
	        	mResult.setMess("Đổi pass thất bại, vui lòng kiểm tra ID, Pass");
	        	mResult.setResult("failed");
	        	mResult.setIssuccess(false);
	        }

	        logtag.info("\n Kết thúc");  
	    } catch (Exception e) {
	       logtag.info("\n Lỗi khi excute login - line 148: " +e);
	    } finally {
	        if (stmt != null) {
	        	try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
	        	}
	    }
	    logtag.info("\n Giá trị result: "+ mResult.getMess() +" Result:"+mResult.getResult()+ " issucess:"+mResult.getIssuccess());
		return mResult;
	}
}
